package models;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import product.merchant.ProductSpecMerchant;
import util.common.ConvertUtil;

import javax.persistence.*;
import java.util.List;
import java.util.Map;

/**
 * Created by liming on 16/7/26.
 * 出库单产品明细
 */
@Entity
@Table(name = "admin_stock_out_product")
public class AdminStockOutProduct extends BaseModel {
    /**
     * 出库单
     */
    @ManyToOne
    @JoinColumn(name = "admin_stock_out_id")
    public AdminStockOut adminStockOut;

    /**
     * 关联规格产品
     */
    @ManyToOne
    @JoinColumn(name = "product_spec_merchant_id")
    public ProductSpecMerchant productSpecMerchant;

    /**
     * 销售数量
     */
    @Column(name = "num")
    public Double num;

    /**
     * 销售价格
     */
    @Column(name = "price")
    public Double price;

    /**
     * 总金额
     */
    @Column(name = "amount")
    public Double amount;

    /**
     * 加载出货明细
     * @return
     */
    public final static String SQL_STOCK_OUT_PRODUCT_SPEC=
            "select  b.d_code, b.bar_code , " +
                    "case b.product_classify " +
                    "when 'BASIC_PRODUCT' then '基础产品'" +
                    "when 'FINISHED_PRODUCT' then '成品'" +
                    "when 'ACCESSORY' then '辅料'" +
                    "when 'COMBO_PRODUCT' then '套餐'" +
                    "else '' end as product_classify," +
                    "c.name," +
                    "b.spec," +
                    "b.unit," +
                    "a.buy_number," +
                    "if(a.buy_number=0,0,a.amount/a.buy_number) as sale_price," +
                    "a.amount , e.buy_number_group " +
             " from (" +
                        " select e.product_spec_id," +
                        " ifnull(sum(b.buy_number),0) as buy_number," +
                        " ifnull(sum(b.amount),0) as amount" +
                        " from admin_order a" +
                        " left join admin_order_item b on a.id = b.admin_order" +
                        " left join goods c on b.goods_id = c.id" +
                        " left join product_spec_merchants e on c.serial_id = e.id" +
                        " where  b.buy_number!=0 %s" +
                        " group by e.product_spec_id" +
                    ") a" +
            "   left join product_specs b on a.product_spec_id = b.id" +
            "   left join products c on b.product_id = c.id " +
            "   left join admin_product_type d on c.admin_product_type_id = d.id " +
            "   left join ( " +
            "     select a.id , a.spec , GROUP_CONCAT(a.buy_number, a.unit , '共: ',a.total ,'单 ') as buy_number_group " +
            "  from ( " +
            "    select e.id  , e.spec , e.unit , b.buy_number , sum(1) as total " +
            "    from admin_order a  " +
            "    left join admin_order_item b on a.id = b.admin_order  " +
            "    left join goods c on b.goods_id = c.id " +
            "    left join product_spec_merchants d on c.serial_id = d.id " +
            "    left join product_specs e on d.product_spec_id = e.id " +
            "     where b.buy_number !=0 %s " +
            "    group by e.id , e.spec, b.buy_number order by e.id , b.buy_number " +
            "   ) a group by  a.id" +
            "   ) e on a.product_spec_id = e.id" +
            "  order by d.code  ;";
    public static List<Map<String,Object>> loadStockOutProductSpec(long adminStockOutId,String orderIds){
        String sql = "";
        String sqlWhere = "";
        if(adminStockOutId>0) {
            sqlWhere = " and a.admin_stock_out_id = " + adminStockOutId;
            sql = String.format(SQL_STOCK_OUT_PRODUCT_SPEC, sqlWhere ,sqlWhere);
        } else {
            sqlWhere = " and a.id in (" + ConvertUtil.escapeSql(orderIds) + ")";
            sql = String.format(SQL_STOCK_OUT_PRODUCT_SPEC, sqlWhere ,sqlWhere);
        }
        Query query = em().createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> resultList = query.getResultList();
        
        return resultList;
    }

    /**
     * 加载出货拆分明细
     * @return
     */
    public final static String SQL_STOCK_OUT_PRODUCT_SPEC_SPLITE =
            "select  b.d_code," +
                    "c.name," +
                    "b.spec," +
                    "b.unit," +
                    "a.num," +
                    "ifnull(d.stock_num,0) as stock_num," +
                    "ifnull(d.stock_num,0)-a.num as diff_num," +
                    "case b.product_classify " +
                    "when 'BASIC_PRODUCT' then '基础产品'" +
                    "when 'FINISHED_PRODUCT' then '成品'" +
                    "when 'ACCESSORY' then '辅料'" +
                    "when 'COMBO_PRODUCT' then '套餐'" +
                    "else '' end as product_classify" +
            " from(" +
                    "select split_product_spec_id," +
                    "SUM(splite_num) as num" +
                    " from (" +
                        "select if(f.product_classify = 'COMBO_PRODUCT' AND g.id is not null,g.split_product_spec_id,f.id) as split_product_spec_id," +
                                "if(f.product_classify = 'COMBO_PRODUCT' ,IFNULL(b.buy_number,0)*IFNULL(g.split_num,0),IFNULL(b.buy_number,0)) as splite_num" +
                        " from admin_order a" +
                        " left join admin_order_item b on a.id = b.admin_order" +
                        " left join goods c on b.goods_id = c.id" +
                        " left join product_spec_merchants e on c.serial_id = e.id" +
                        " left join product_specs f on e.product_spec_id = f.id" +
                        " left join product_spec_split g on f.id = g.combo_product_spec_id" +
                        " left join product_specs h on g.split_product_spec_id = g.id" +
                        " where %s" +
                    ") a" +
                    " group by split_product_spec_id" +
            " ) a left join product_specs b on a.split_product_spec_id = b.id" +
            " left join products c on b.product_id = c.id" +
            " left join admin_product_spec_stock d on b.id = d.product_spec_id;";
    public static List<Map<String,Object>> loadStockOutProductSpecSplite(long adminStockOutId,String orderIds){
        String spliteSql= "";
        if(adminStockOutId>0)
            spliteSql = String.format(SQL_STOCK_OUT_PRODUCT_SPEC_SPLITE,"a.admin_stock_out_id = " + adminStockOutId);
        else
            spliteSql = String.format(SQL_STOCK_OUT_PRODUCT_SPEC_SPLITE,"a.id in (" + ConvertUtil.escapeSql(orderIds) +")");
        Query query = em().createNativeQuery(spliteSql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> resultList = query.getResultList();
        return resultList;
    }

}
